﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Jacaranda.Data;
using System.Data;
using System.Data.Common;

namespace Jacaranda.Model
{
    public class Database
    {
        private static string connectionString;
        public static string ConnectionString
        {
            get { return connectionString; }
            set
            {
                connectionString = value;
                DataPortal.ConnectionString = connectionString;
            }
        }

        private static DatabaseType databaseType;
        public static DatabaseType DatabaseType
        {
            get { return databaseType; }
            set
            {
                databaseType = value;

                DataPortal.DatabaseType = databaseType;
                AssociationTableTimestampColumnName = DataAccess.Create().AssociationTableTimestampColumnName;
            }
        }

        internal static string AssociationTableTimestampColumnName { get; set; }


        public static void Clear()
        {
            Metadata.RegisteredTypes.Where(i => i.IsTable).ToList().ForEach(i => DeleteDataFromTable(Metadata.Type(i.Type).TableName));
            Metadata.Associations.ForEach(i => DeleteDataFromTable(i.TableName));
        }

        private static void DeleteAllTables()
        {
            Metadata.RegisteredTypes.Where(i => i.IsTable).ToList().ForEach(i => DropTable(Metadata.Type(i.Type).TableName));
            Metadata.Associations.ForEach(i => DropTable(i.TableName));
        }

        private static void DeleteDataFromTable(string tableName)
        {
            DataAccess.Create().DeleteTableData(tableName);
        }

        private static void DropTable(string tableName)
        {
            DataAccess.Create().DropTable(tableName);
        }

        public static void DropFunction(string functionName)
        {
            DataAccess.Create().DropFunction(functionName);
        }



        public static void Create()
        {
            Clear();
            DeleteAllTables();

            Metadata.RegisteredTypes.Where(i => i.IsTable).ToList().ForEach(i =>
                {
                    if (i.Create)
                    {
                        DataPortal.Execute(GetCreateEntityTableStatement(i.Type));
                    }
                });

            Metadata.Associations.ForEach(i =>
                {
                    if (Metadata.Type(i.From).Create && Metadata.Type(i.To).Create)
                    {
                        DataPortal.Execute(GetCreateAssociationTableStatement(i));
                    }
                });
        }

        public static void Update()
        {
            Metadata.RegisteredTypes.ForEach(i => DataPortal.Execute(string.Format("IF NOT EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'{0}') AND type = (N'U')) BEGIN\r\n{1}END\r\n", Metadata.Type(i.Type).TableName, GetCreateEntityTableStatement(i.Type))));
            Metadata.Associations.ForEach(i => DataPortal.Execute(string.Format("IF NOT EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'{0}') AND type = (N'U')) BEGIN\r\n{1}END\r\n", i.TableName, GetCreateAssociationTableStatement(i))));
        }

        public static string GenerateScript()
        {
            StringBuilder b = new StringBuilder();

            Metadata.RegisteredTypes.ForEach(i => b.Append(GetCreateEntityTableStatement(i.Type)).Append("GO;\r\n\r\n"));
            Metadata.Associations.ForEach(i => b.Append(GetCreateAssociationTableStatement(i)).Append("GO;\r\n\r\n"));

            return b.ToString();
        }

        public static void ExecuteScript(string sql)
        {
            foreach (string line in sql.Split(new string[] { "GO\r\n" }, StringSplitOptions.None).Where(i => !string.IsNullOrWhiteSpace(i)))
            {
                DataPortal.Execute(line);
            }
        }

        public static void ExecuteScript(string sql, Transaction tran)
        {
            foreach (string line in sql.Split(new string[] { "GO\r\n" }, StringSplitOptions.None).Where(i => !string.IsNullOrWhiteSpace(i)))
            {
                DataPortal.Execute(line, tran);
            }
        }

        public static object SelectScalar(string sql)
        {
            return DataPortal.SelectScalar(sql);
        }

        public static T[] Select<T>(string sql)
        {
            return Mapper.RelationToEntity(DataPortal.Select(sql), typeof(T)).Cast<T>().ToArray();
        }

        public static T SelectOne<T>(string sql) where T : Entity, new()
        {
            List<Entity> entities = Mapper.RelationToEntity(DataPortal.Select(sql), typeof(T));
            return (entities.Count == 0) ? null : (T)entities[0];
        }


        public static Transaction BeginTransaction()
        {
            return DataPortal.BeginTransaction();
        }

        public static InClause In<T1, T2>(ConditionalExpression condition1, ConditionalExpression condition2)
        {
            return new InClause() 
            { 
                Type1 = typeof(T1), 
                Type2 = typeof(T2), 
                Condition1 = condition1,
                Condition2 = condition2,
                IsTopMost = true 
            };
        }

        public static InClause In<T1, T2>(ConditionalExpression condition1)
        {
            return In<T1, T2>(condition1, null);
        }

        public static InClause In<T1, T2>()
        {
            return In<T1, T2>(null, null);
        }

        public static InClause In(Type type1, Type type2, ConditionalExpression condition1, ConditionalExpression condition2)
        {
            return new InClause()
            { 
                Type1 = type1, 
                Type2 = type2, 
                Condition1 = condition1, 
                Condition2 = condition2, 
                IsTopMost = true 
            };
        }

        private static InClause In<T1, T2>(string associationPropertyName, bool isDemandingSource, ConditionalExpression condition1, ConditionalExpression condition2)
        {
            return new InClause() 
            { 
                Type1 = typeof(T1),
                Type2 = typeof(T2), 
                Instance = Metadata.Type(typeof(T1)).AssociationFields.First(i => i.PropertyName == associationPropertyName).Association.Instance, 
                Condition1 = condition1, 
                Condition2 = condition2, 
                IsTopMost = true,
                IsDemandingSource = isDemandingSource
            };
        }

        public static InClause In<T1, T2>(string associationPropertyName, ConditionalExpression condition1, ConditionalExpression condition2)
        {
            if (typeof(T1).Equals(typeof(T2)))
            {
                throw new InvalidOperationException("Jacaranda.Database.In: T1 and T2 cannot be the same. Call InSource or InTarget instead.");
            }

            return In<T1, T2>(associationPropertyName, true, condition1, condition2);
        }

        public static InClause InSource<T1, T2>(string associationPropertyName, ConditionalExpression condition1, ConditionalExpression condition2)
        {
            if (!typeof(T1).Equals(typeof(T2)))
            {
                throw new InvalidOperationException("Jacaranda.Database.InSource: T1 and T2 must be the same. Call In instead.");
            }

            return In<T1, T2>(associationPropertyName, true, condition1, condition2);
        }

        public static InClause InSource<T1, T2>(string associationPropertyName)
        {
            return InSource<T1, T2>(associationPropertyName, null, null);
        }

        public static InClause InTarget<T1, T2>(string associationPropertyName, ConditionalExpression condition1, ConditionalExpression condition2)
        {
            if (!typeof(T1).Equals(typeof(T2)))
            {
                throw new InvalidOperationException("Jacaranda.Database.InTarget: T1 and T2 must be the same. Call In instead.");
            }

            return In<T1, T2>(associationPropertyName, false, condition1, condition2);
        }

        public static InClause InTarget<T1, T2>(string associationPropertyName)
        {
            return InTarget<T1, T2>(associationPropertyName, null, null);
        }

        public static NoClause No<T1, T2>()
        {
            return new NoClause()
            {
                Type1 = typeof(T1),
                Type2 = typeof(T2),
                IsTopMost = true
            };
        }



        public static void Update<T>(ConditionalExpression condition, params Update[] updates) where T : Entity, new()
        {
            Update(typeof(T), condition, updates);
        }

        private static void Update(Type entityType, ConditionalExpression condition, params Update[] updates)
        {
            DataPortal.Execute(condition.CreateUpdateCommand(entityType, updates));
        }



        public static void Delete<T>() where T : Entity, new()
        {
            Delete(typeof(T));
        }

        private static void Delete(Type entityType)
        {
            Transaction tran = DataPortal.BeginTransaction();

            try
            {
                foreach (EntityAssociation association in Metadata.Type(entityType).Associations)
                {
                    DataPortal.Execute(string.Format("DELETE FROM {0}", association.TableName), tran);
                }

                DataPortal.Execute(string.Format("DELETE FROM {0}", Metadata.Type(entityType).TableName), tran);
                tran.Commit();
            }
            catch
            {
                tran.Rollback();
                throw;
            }
        }

        public static void DeleteNoTransaction<T>(ConditionalExpression condition) where T : Entity, new()
        {
            if (condition == null)
            {
                throw new ArgumentException("Jacaranda.Database.DeleteNoTransaction<T>: condition is null.");
            }

            DbCommand command = condition.CreateDeleteCommand(typeof(T));
            DataPortal.Execute(command);
            command.Dispose();
        }

        public static void Delete<T>(ConditionalExpression condition) where T : Entity, new()
        {
            if (condition == null)
            {
                throw new ArgumentException("Jacaranda.Database.Delete<T>: condition is null.");
            }

            Transaction tran = DataPortal.BeginTransaction();
            List<EntityAssociation> visitedTypes = new List<EntityAssociation>();

            try
            {
                Delete(typeof(T), condition, tran, visitedTypes, false);
                tran.Commit();
            }
            catch
            {
                tran.Rollback();
                throw;
            }
        }

        public static void CascadeDelete<T>(ConditionalExpression condition) where T : Entity, new()
        {
            if (condition == null)
            {
                throw new ArgumentException("Jacaranda.Database.CascadeDelete<T>: condition is null.");
            }

            Transaction tran = DataPortal.BeginTransaction();
            List<EntityAssociation> visitedTypes = new List<EntityAssociation>();

            try
            {
                Delete(typeof(T), condition, tran, visitedTypes, true);
                tran.Commit();
            }
            catch
            {
                tran.Rollback();
                throw;
            }
        }

        private static void Delete(Type entityType, ConditionalExpression condition, Transaction tran, List<EntityAssociation> visitedTypes, bool isCascadeDelete)
        {
            DataPortal.Execute(condition.CreateDeleteCommand(entityType), tran);
            DeleteEntityAssociations(entityType, tran, visitedTypes, isCascadeDelete);
        }

        private static void Delete(Type entityType, EntityAssociation association, string sql, Transaction tran, List<EntityAssociation> visitedTypes)
        {
            if (visitedTypes.FirstOrDefault(i => i.From.Equals(association.From) && i.To.Equals(association.To) && i.Instance == association.Instance) != null)
            {
                return;
            }

            visitedTypes.Add(association);

            DataPortal.Execute(sql, tran);
            DeleteEntityAssociations(entityType, tran, visitedTypes, true);
        }

        private static void DeleteEntityAssociations(Type entityType, Transaction tran, List<EntityAssociation> visitedTypes, bool isCascadeDelete)
        {
            string fieldName = Metadata.Type(entityType).PrimaryKey.FieldName;
            string tableName = Metadata.Type(entityType).TableName;

            foreach (EntityAssociation association in Metadata.Type(entityType).Associations)
            {
                if (association.From.Equals(entityType) && association.To.Equals(entityType))
                {
                    if (isCascadeDelete)
                    {
                        Delete(association.To,
                            association,
                            string.Format("DELETE FROM {0} WHERE {1} IN (SELECT Key2 FROM {2} WHERE Key1 NOT IN (SELECT {3} FROM {4}))",
                            Metadata.Type(association.To).TableName,
                            Metadata.Type(association.To).PrimaryKey.FieldName,
                            association.TableName,
                            fieldName,
                            tableName),
                            tran,
                            visitedTypes);

                        Delete(association.From,
                          association,
                          string.Format("DELETE FROM {0} WHERE {1} IN (SELECT Key2 FROM {2} WHERE Key1 NOT IN (SELECT {3} FROM {4}))",
                          Metadata.Type(association.From).TableName,
                          Metadata.Type(association.From).PrimaryKey.FieldName,
                          association.TableName,
                          fieldName,
                          tableName),
                          tran,
                          visitedTypes);
                    }

                    DataPortal.Execute(string.Format("DELETE FROM {0} WHERE Key1 NOT IN (SELECT {1} FROM {2}) OR Key2 NOT IN (SELECT {3} FROM {4})", association.TableName, fieldName, tableName, fieldName, tableName), tran);
                }
                else if (association.From.Equals(entityType))
                {
                    if (isCascadeDelete)
                    {
                        Delete(association.To,
                            association,
                            string.Format("DELETE FROM {0} WHERE {1} IN (SELECT Key2 FROM {2} WHERE Key1 NOT IN (SELECT {3} FROM {4}))", 
                            Metadata.Type(association.To).TableName, 
                            Metadata.Type(association.To).PrimaryKey.FieldName,
                            association.TableName,
                            fieldName, 
                            tableName), 
                            tran, 
                            visitedTypes);
                    }

                    DataPortal.Execute(string.Format("DELETE FROM {0} WHERE Key1 NOT IN (SELECT {1} FROM {2})", association.TableName, fieldName, tableName), tran);
                }
                else
                {
                    if (isCascadeDelete)
                    {
                        Delete(association.From,
                            association,
                            string.Format("DELETE FROM {0} WHERE {1} IN (SELECT Key2 FROM {2} WHERE Key1 NOT IN (SELECT {3} FROM {4}))",
                            Metadata.Type(association.From).TableName,
                            Metadata.Type(association.From).PrimaryKey.FieldName,
                            association.TableName,
                            fieldName,
                            tableName),
                            tran,
                            visitedTypes);
                    }

                    DataPortal.Execute(string.Format("DELETE FROM {0} WHERE Key2 NOT IN (SELECT {1} FROM {2})", association.TableName, fieldName, tableName), tran);
                }
            }
        }


        public static HasClause Has(Type searchType, ConditionalExpression condition)
        {
            return new HasClause()
            {
                SearchType = searchType,
                Condition = condition
            };
        }

        public static HasClause Has<T>(ConditionalExpression condition)
        {
            return new HasClause()
            {
                SearchType = typeof(T),
                Condition = condition
            };
        }

        public static ContainsClause Contains(Type type1, Type type2, string property1, string property2, ConditionalExpression condition)
        {
            return new ContainsClause()
            {
                Type1 = type1,
                Type2 = type2,
                Property1 = property1,
                Property2 = property2,
                Condition = condition
            };
        }


        public static bool Exists<T>(ConditionalExpression expression) where T : Entity, new()
        {
            return Exists<T>(expression, null);
        }

        public static bool Exists<T>(ConditionalExpression expression, Transaction transaction) where T : Entity, new()
        {
            DbCommand command = expression.CreateExistsCommand(typeof(T));
            bool exists = (int)((transaction == null) ? DataPortal.SelectScalar(command) : DataPortal.SelectScalar(command, transaction)) == 1;
            command.Dispose();
            
            return exists;
        }

        public static T[] Search<T>(ConditionalExpression expression) where T : Entity, new()
        {
            return Search<T>(expression, false, null, null, null);
        }

        public static T[] Search<T>(ConditionalExpression expression, Transaction transaction) where T : Entity, new()
        {
            return Search<T>(expression, false, null, null, transaction);
        }

        public static T[] Search<T>(ConditionalExpression expression, SortCriteria sort) where T : Entity, new()
        {
            return Search<T>(expression, false, sort, null, null);
        }

        public static T[] Search<T>(ConditionalExpression expression, SortCriteria sort, Transaction transaction) where T : Entity, new()
        {
            return Search<T>(expression, false, sort, null, transaction);
        }

        public static T[] Search<T>(ConditionalExpression expression, SortCriteria sort, SearchPaginationCriteria page) where T : Entity, new()
        {
            return Search<T>(expression, false, sort, page, null);
        }

        private static T[] Search<T>(ConditionalExpression expression, bool isTopOne, SortCriteria sort, SearchPaginationCriteria page, Transaction transaction) where T : Entity, new()
        {
            if (Metadata.Type(typeof(T)).IsTable)
            {
                return SearchType(typeof(T), expression, isTopOne, sort, page, transaction).Cast<T>().ToArray();
            }
            else
            {
                List<T> results = new List<T>();

                foreach (EntityMetadata m in Metadata.Type(typeof(T)).SubTypes)
                {
                    results.AddRange(SearchType(m.Type, expression, isTopOne, sort, page, transaction).Cast<T>());
                }

                return results.ToArray();
            }
        }

        public static Entity[] Search(Type type, ConditionalExpression expression, SortCriteria sort, SearchPaginationCriteria page, Transaction transaction)
        {
            if (Metadata.Type(type).IsTable)
            {
                return SearchType(type, expression, false, sort, page, transaction);
            }
            else
            {
                List<Entity> results = new List<Entity>();

                foreach (EntityMetadata m in Metadata.Type(type).SubTypes)
                {
                    results.AddRange(SearchType(m.Type, expression, false, sort, page, transaction));
                }

                return results.ToArray();
            }
        }

        private static Entity[] SearchType(Type type, ConditionalExpression expression, bool isTopOne, SortCriteria sort, SearchPaginationCriteria page, Transaction transaction)
        {
            DbCommand command = expression.CreateSelectEntityCommand(type, isTopOne, sort, page);

            List<Entity> results = Mapper.RelationToEntity((transaction == null) ? DataPortal.Select(command) : DataPortal.Select(command, transaction), type);

            if (transaction != null)
            {
                results.ForEach(i => i.SetTransaction(transaction));
            }

            command.Dispose();


            return results.ToArray();
        }


        public static T SearchOne<T>(ConditionalExpression expression) where T : Entity, new()
        {
            return SearchOne<T>(expression, null, null);
        }

        public static T SearchOne<T>(ConditionalExpression expression, Transaction transaction) where T : Entity, new()
        {
            return SearchOne<T>(expression, null, transaction);
        }

        public static T SearchOne<T>(ConditionalExpression expression, SortCriteria sort) where T : Entity, new()
        {
            T[] results = Search<T>(expression, true, sort, null, null);
            return (results.Length == 0) ? null : results[0];
        }

        public static T SearchOne<T>(ConditionalExpression expression, SortCriteria sort, Transaction transaction) where T : Entity, new()
        {
            T[] results = Search<T>(expression, true, sort, null, transaction);
            return (results.Length == 0) ? null : results[0];
        }


        public static T[] All<T>() where T : Entity
        {
            return Mapper.RelationToEntity(DataPortal.Select(string.Format("SELECT * FROM {0}", Metadata.Type(typeof(T)).TableName)), typeof(T))
                .Cast<T>()
                .ToArray();
        }

        public static T[] All<T>(SortCriteria sort, SearchPageCriteria page) where T : Entity, new()
        {
            return Search<T>(new SelectAllCriteria(), sort, page);
        }

        public static object[] All(Type type, SortCriteria sort, SearchPageCriteria page)
        {
            return Search(type, new SelectAllCriteria(), sort, page, null);
        }


        public static int Count<T>(ConditionalExpression expression) where T : Entity
        {
            return Count(typeof(T), expression, null);
        }

        public static int Count<T>(ConditionalExpression expression, Transaction transaction) where T : Entity
        {
            return Count(typeof(T), expression, transaction);
        }

        public static int Count(Type type, ConditionalExpression expression)
        {
            return Count(type, expression);
        }

        public static int Count(Type type, ConditionalExpression expression, Transaction transaction)
        {
            int count = 0;

            foreach (EntityMetadata m in Metadata.Type(type).RelatedEntities)
            {
                DbCommand command = expression.CreateSelectCountCommand(m.Type);
                count += (int)((transaction == null) ? DataPortal.SelectScalar(command) : DataPortal.SelectScalar(command, transaction));
                command.Dispose();
            }

            return count;
        }

        public static int Count<T>() where T : Entity
        {
            return Count(typeof(T), (Transaction)null);
        }

        public static int Count<T>(Transaction transaction) where T : Entity
        {
            return Count(typeof(T), transaction);
        }

        public static int Count(Type type, Transaction transaction)
        {
            string sql = string.Format("SELECT COUNT(*) FROM {0}", Metadata.Type(type).TableName);
            DataTable table = (transaction == null) ? DataPortal.Select(sql) : DataPortal.Select(sql, transaction);

            return table.Rows[0].Field<int>(0);
        }


        public static T Get<T>(object keyValue) where T : Entity
        {
            return Get(typeof(T), keyValue, null) as T;
        }

        public static object Get(Type type, object keyValue)
        {
            return Get(type, keyValue, null);
        }

        public static T Get<T>(object keyValue, Transaction tran) where T : Entity
        {
            return Get(typeof(T), keyValue, tran) as T;
        }

        public static object Get(Type type, object keyValue, Transaction tran)
        {
            foreach (EntityMetadata m in Metadata.Type(type).RelatedEntities)
            {
                string sql = string.Format("SELECT TOP 1 * FROM {0} WHERE {1}='{2}'",
                m.TableName,
                m.PrimaryKey.FieldName,
                keyValue);

                DataTable dt = (tran == null) ? DataPortal.Select(sql) : DataPortal.Select(sql, tran);

                if (dt.Rows.Count > 0)
                {
                    return Mapper.RelationToEntity(dt, m.Type)[0];
                }
            }

            return null;
        }


        public static T GetByDbKey<T>(int keyValue) where T : Entity
        {
            return GetByDbKey(typeof(T), keyValue, null) as T;
        }

        public static object GetByDbKey(Type type, int keyValue, Transaction tran)
        {
            foreach (EntityMetadata m in Metadata.Type(type).RelatedEntities)
            {
                string sql = string.Format("SELECT TOP 1 * FROM {0} WHERE {1}={2}",
                m.TableName,
                m.DbPrimaryKey.FieldName,
                keyValue);

                DataTable dt = (tran == null) ? DataPortal.Select(sql) : DataPortal.Select(sql, tran);

                if (dt.Rows.Count > 0)
                {
                    return Mapper.RelationToEntity(dt, m.Type)[0];
                }
            }

            return null;
        }


        private static string GetCreateEntityTableStatement(Type type)
        {
            StringBuilder b = new StringBuilder();
            EntityMetadata m = Metadata.Type(type);

            b.Append("CREATE TABLE ")
             .Append(m.TableName)
             .Append("(\r\n");


            FieldMetadata[] fields = m.Fields;
            for (int i = 0; i <= fields.Length - 1; i++)
            {
                b.Append(fields[i].FieldName)
                 .Append(" ")
                 .Append(fields[i].GetDatabaseType())
                 .Append(fields[i].IsDbPrimaryKey ? " PRIMARY KEY" : null)
                 .Append(fields[i].IsAutoIncrease ? " IDENTITY(1,1)" : null)
                 .Append((i < fields.Length - 1) ? ", " : ");")
                 .Append("\r\n");
            }

            foreach (FieldMetadata field in m.Fields.Where(i => i.IsIndex))
            {
                b.Append(string.Format("CREATE NONCLUSTERED INDEX {0}_{1} ON {2} ({3});\r\n", m.TableName, field.FieldName, m.TableName, field.FieldName));
            }

            return b.ToString(); ;
        }

        private static string GetCreateAssociationTableStatement(EntityAssociation association)
        {
            return string.Format("CREATE TABLE {0} (AutoID INT IDENTITY(1,1) PRIMARY KEY, Key1 {2}, Key2 {3});\r\n", 
                association.TableName,
                Database.AssociationTableTimestampColumnName,
                Metadata.Type(association.From).PrimaryKey.GetDatabaseType(),
                Metadata.Type(association.To).PrimaryKey.GetDatabaseType());
        }
    }
}
